package com.dao.impl;

import com.dao.ISupplierBlackListDao;
import com.pojo.SupplierBlackList;
import com.pojo.SupplierWhiteList;
import com.util.DBHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


public class SupplierBlackListDaoImpl implements ISupplierBlackListDao {
    private DBHelper db = new DBHelper();
    // 新增黑名单
    @Override
    public int insertBlackList(SupplierBlackList blackList) {
        String sql = "insert into supplierBlackList values(null,?, ?, ?, ?)";
        Object[] objs={blackList.getEnterpriseId(), blackList.getSupplierId(),
                blackList.getReason(), blackList.getUpdateDate()};
        return db.dtUpdate(sql,objs);
    }
    // 查询企业的黑名单列表（分页）
    @Override
    public ArrayList<SupplierBlackList> selectBlackListByEnterpriseId(Integer enterpriseId,Integer pageIndex) {
        String sql = "select * from supplierBlackList where enterpriseId=? limit ?,5";
        int index = (pageIndex-1)*5;
        return db.dtQuery(sql,SupplierBlackList.class,enterpriseId,index);

    }

    //查询企业黑名单总数
    @Override
    public int selectBlackListCount(Integer enterpriseId) {
        String sql = "select count(*) from supplierBlackList where enterpriseId = ?";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql);
            ps.setInt(1, enterpriseId);
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeAll(con, ps, resultSet);
        }
        return 0;
    }
    // 删除黑名单（解除时用）
    @Override
    public int deleteBlackList(Integer enterpriseId, Integer supplierId) {
        String sql = "delete from supplierBlackList where enterpriseId = ? and supplierId = ?";
        return db.dtUpdate(sql, enterpriseId, supplierId);
    }

    // 检查企业是否已在黑名单中
    @Override
    public boolean checkInBlackList(Integer enterpriseId, Integer supplierId) {
        String sql = "select * from supplierBlackList where enterpriseId = ? and supplierId = ?";
        ArrayList list = db.dtQuery(sql, SupplierBlackList.class, enterpriseId, supplierId);
        return !list.isEmpty();
    }
    // 按条件查询黑名单（企业名称模糊查询）
    @Override
    public ArrayList<SupplierBlackList> selectBlackListByCondition(Integer enterpriseId, String supplierName,String supplierLevel,Integer pageIndex)  {
        StringBuilder sql = new StringBuilder("select sbl.* from supplierBlackList sbl " +
                "join enterprise e ON sbl.supplierId = e.id " +
                "where sbl.enterpriseId = ?");
        ArrayList<Object> params = new ArrayList<>();
        params.add(enterpriseId);

        if (supplierName != null && !supplierName.trim().isEmpty()) {
            sql.append(" and e.name LIKE ?");
            params.add("%" + supplierName + "%");
        }
        if (supplierLevel != null && !supplierLevel.isEmpty()) {
            sql.append(" and sbl.supplierLevel = ?");
            params.add(supplierLevel);
        }
        sql.append(" limit ?, 5");
        int index = (pageIndex-1)*5;
        params.add(index);
        return db.dtQuery(sql.toString(), SupplierBlackList.class, params.toArray());

    }

    // 按条件查询黑名单总数
    @Override
    public int selectBlackListCountByCondition(Integer enterpriseId, String supplierName, String supplierLevel) {
        StringBuilder sql = new StringBuilder("select count(*) from supplierBlackList sbl " +
                "join enterprise e ON sbl.supplierId = e.id " +
                "where sbl.enterpriseId = ?");
        ArrayList<Object> params = new ArrayList<>();
        params.add(enterpriseId);

        if (supplierName != null && !supplierName.trim().isEmpty()) {
            sql.append(" and e.name LIKE ?");
            params.add("%" + supplierName + "%");
        }
        if(supplierLevel != null && !supplierLevel.trim().isEmpty()) {
            sql.append(" and sbl.supplierLevel = ?");
            params.add(supplierLevel);
        }

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeAll(con, ps, resultSet);
        }
        return 0;
    }

}



